<?php
	class ModelKnowledgeKnowledge extends Model {
		public function addKnowledge($data) {
			$row = $this->db->query("INSERT INTO knowledge SET user_id='".$this->db->escape($data['user_id'])."',kname ='".$this->db->escape($data['kname'])."',ftime='".$this->db->escape($data['ftime'])."',kdetail='".$this->db->escape($data['kdetail'])."'");// 
			if($row){
				$kid = $this->db->getLastId();
				$lid = $this->addLabel($data['klabel'],$kid);
				return $kid;
			}else{
				return false;
			}
		}
		public function addLabel($data,$kid) {
			$temp = explode(",",$data);
			for ($i=0; $i < sizeof($temp); $i++) {
				$this->db->query("INSERT INTO label SET kid='".$this->db->escape($kid)."', user_id='".$this->session->data['user_id']."', times='".($i+1)."', label='".$this->db->escape($temp[$i])."'");
			}
			$lid = $this->db->getLastId();
			return $lid;
			
		}
		public function editKnowledge($data){
			$this->db->query("UPDATE knowledge SET user_id='".$this->db->escape($data['user_id'])."',kname ='".$this->db->escape($data['kname'])."',kdetail='".$this->db->escape($data['kdetail'])."' WHERE id = '".(int)$data['kid'] . "'");
			$this->editLabel($this->db->escape($data['label']),(int)$data['kid']);
			$lid = $this->db->getLastId();
			return $lid;
		}
		public function editLabel($data,$kid){
			$this->db->query("DELETE FROM  label WHERE kid = '".(int)$kid."'");
			$this->addLabel($data,$kid);

		}
		public function getLabelByKid($kid) {
			$query = $this->db->query("SELECT label FROM label WHERE kid = '" . (int)$kid ."'");
			return $query->rows;
		}
		public function getFirsttimebykid($kid) {
			$query = $this->db->query("SELECT ftime FROM knowledge WHERE id = '" .$this->db->escape($kid) . "'");
			return $query->row['ftime'];
		}
		public function getKnowledgesByUidForView($data){
			if(!isset($data['user_id'])){
				$data['user_id'] = $this->session->data['user_id'];
			}
			$sqlcount = "SELECT (COUNT(*)) AS total";
			$sqlselect = "SELECT k.id,k.kname,k.kdetail,c.id AS cid,c.date,c.times,c.star";
			$sql = " FROM knowledge k LEFT JOIN label l ON (k.id = l.kid) LEFT JOIN complate c ON (k.id = c.kid) WHERE k.user_id = '" . $this->db->escape($data['user_id']) ."'";
			if (isset($data['kname'])) {
				$sql .= " AND k.kname LIKE '%" . $this->db->escape($data['kname']) . "%'";
			}
			if (isset($data['label'])) {
				$sql .= " AND l.label LIKE '%". $this->db->escape($data['label']) . "%'";
			}
			if (isset($data['times'])) {
				$sql .= " AND c.times = '". $this->db->escape($data['times']) . "'";
			}
			if (isset($data['star'])) {
				$sql .= " AND c.star = '". $this->db->escape($data['star']) . "'";
			}
			if (isset($data['todaystarttime'])) {
				$sql .= " AND c.date > '" . $this->db->escape($data['todaystarttime']) . "'";
			}
			if (isset($data['todayendtime'])) {
				$sql .= " AND c.date < '" . $this->db->escape($data['todayendtime']) . "'";
			}
			$sql.=" GROUP BY c.id";
			
			$sort_data = array(
				'kname'
			);
			if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
				$sql .= " ORDER BY k." . $data['sort'];
			} else {
				$sort_data_c = array('date',
				'star','times');
				if (isset($data['sort']) && in_array($data['sort'], $sort_data_c)) {
					$sql .= " ORDER BY c." . $data['sort'];
				} else {
				$sql .= " ORDER BY c.date";
				}
			}
			

			if (isset($data['order']) && ($data['order'] == 'DESC')) {
				$sql .= " DESC";
			} else {
				$sql .= " ASC";
			}
			$sqlcount .= $sql;
			$total = $this->db->query($sqlcount);
			if (isset($data['start']) || isset($data['limit'])) {
				if ($data['start'] < 0) {
					$data['start'] = 0;
				}

				if ($data['limit'] < 1) {
					$data['limit'] = 10;
				}

				$sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
			}
			$sqlselect .= $sql;
			$query = $this->db->query($sqlselect);
			$results = array(
				'maindata'=>$query->rows,
				'total' => $total->num_rows
				); 
			return $results;//  $sqlselect;//$data;//
		}
		public function getKnowledgesByUid($data){
			$sqlcount = "SELECT (SELECT COUNT(*) FROM knowledge) AS total";
			$sqlselect = "SELECT DISTINCT k.*,(SELECT COUNT(*) FROM complate WHERE kid = k.id AND star >'0') AS iscomplate , (SELECT COUNT(*) FROM complate WHERE kid = k.id AND star ='5') AS ispass";
			$sql = " FROM knowledge k LEFT JOIN label l ON (k.id = l.kid) WHERE k.user_id = '" . $this->db->escape($data['user_id']) ."'";
			if (isset($data['kname'])) {
				$sql .= " AND k.kname LIKE '%" . $this->db->escape($data['kname']) . "%'";
			}
			if (isset($data['label'])) {
				$sql .= " AND l.label LIKE '%". $this->db->escape($data['label']) . "%'";
			}
			if (isset($data['complate'])) {
				$iscomplate = "(SELECT COUNT(*) FROM complate WHERE kid = k.id AND star >'0')";
				if ($data['complate'] == "1"){
					$sql .= " AND ".$iscomplate." = '8'";
				}elseif($data['complate'] == "0"){
					$sql .= " AND ".$iscomplate." < '8'";
				}
			}
			if (isset($data['pass'])) {
				$ispass = "(SELECT COUNT(*) FROM complate WHERE kid = k.id AND star ='5')";
				if ($data['pass'] == "1"){
					$sql .= " AND ".$ispass." = '8'";
				}elseif($data['pass'] == "0"){
					$sql .= " AND ".$ispass." < '8'";
				}
			}
			if (isset($data['starttime'])) {
				$sql .= " AND k.ftime > '" . $this->db->escape($data['starttime']) . "'";
			}
			if (isset($data['endtime'])) {
				$sql .= " AND k.ftime < '" . $this->db->escape($data['endtime']) . "'";
			}
			$sql .=" GROUP BY k.id";
			$sqlcount .= $sql;
			$total = $this->db->query($sqlcount);
			if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
				$sql .= " ORDER BY k." . $data['sort'];
			} else {
				$sql .= " ORDER BY k.ftime";
			}

			if (isset($data['order']) && ($data['order'] == 'DESC')) {
				$sql .= " DESC";
			} else {
				$sql .= " ASC";
			}

			 
			$sort_data = array(
				'kname',
				'ftime'
			);
			if (isset($data['start']) || isset($data['limit'])) {
				if ($data['start'] < 0) {
					$data['start'] = 0;
				}

				if ($data['limit'] < 1) {
					$data['limit'] = 10;
				}

				$sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
			}
			$sqlselect .= $sql;
			$query = $this->db->query($sqlselect);
			$data = array(
				'maindata'=>$query->rows,
				'total' => $total->num_rows
				); 
			return $data;//$sqlcount;//
		}
		public function getKnowledgesOfComplate($data){
			$sql = "SELECT DISTINCT k.kname,k.kdetail,(SELECT COUNT(*) FROM complate WHERE kid = k.id AND star >'0') AS iscomplate FROM knowledge k LEFT JOIN complate c ON (k.id = c.kid) WHERE k.user_id = '" . $this->db->escape($data['user_id']) ."' AND c.date > '" . $this->db->escape($data['todaystarttime'])."' AND c.date < '" . $this->db->escape($data['todayendtime']) . "'";
			$query = $this->db->query($sql);
			return $query->rows;
		}
		public function cleanKnowledge(){
			$query = $this->db->query("TRUNCATE TABLE knowledge");
			$this->db->query("TRUNCATE TABLE label");

			return $query->row;
		}
		public function repeatFtime($kid,$ftime){
			$this->db->query("UPDATE knowledge SET ftime = '" . $this->db->escape($ftime) . "' WHERE id = '" . (int)$kid . "'");
			return true;
		}
		public function removeKnowledge($kid){
			$this->db->query("DELETE FROM  knowledge WHERE id = '".(int)$kid."'");
			$this->db->query("DELETE FROM  label WHERE kid = '".(int)$kid."'");
			return ture;
		}
		public function velkname($kname){
			$sql = "SELECT COUNT(*) as num FROM knowledge WHERE kname = '" . $this->db->escape($kname) . "' AND user_id='".$this->db->escape($this->session->data['user_id'])."'";
			$query = $this->db->query($sql);
			return $query->row;
		}
	}//class